﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;


namespace WindowsFormsApplication1
{
    class AddressEntryDAO
    {

        private SqlConnection db_connection;
        // "Data Source=sparky;Initial Catalog=cps420_team2_poc;Persist Security Info=True;User ID=cps420team2;Password=team2"

        public void Connect_To_Database(string connection_string)
        {
            db_connection = new SqlConnection(connection_string);
            db_connection.Open();
        }

        public void Disconnect_From_Database()
        {
            db_connection.Close();
        }

        public DataTable LoadAllRecords()
        {
            string load_all = "SELECT * FROM AddressEntry";
            SqlDataAdapter ad = new SqlDataAdapter(load_all, db_connection);
            DataTable dbdt = new DataTable();
            ad.Fill(dbdt);
            return dbdt;
        }

        public void UpdateRecord(AddressEntry old_entry, AddressEntry new_entry)
        {
            SqlCommand update_record = new SqlCommand("UPDATE AddressEntry SET Name='" + 
                new_entry.get_name()  +  "', Email='"  +   
                new_entry.get_email_address() + "'WHERE Name='"   +  
                old_entry.get_name()   +  "' AND Email='"  +  
                old_entry.get_email_address()  +  "'", 
                db_connection);

            update_record.ExecuteNonQuery();
        }

        public void CreateNewRecord(AddressEntry entry)
        {
            SqlCommand create_new_record = new SqlCommand("INSERT INTO AddressEntry(Name, Email) " +
                            "Values('"+ entry.get_name() + "', '"+ entry.get_email_address() +"')", db_connection);
            
            create_new_record.ExecuteNonQuery();
        }

        public void DeleteRecord(AddressEntry entry)
        {
            SqlCommand del_rec = new SqlCommand("DELETE FROM AddressEntry WHERE Name='" + entry.get_name() +
                "' AND Email='" + entry.get_email_address() + "'" , db_connection);
            
            del_rec.ExecuteNonQuery();
        }


    }
}
